package com.label.Utils;

import com.alibaba.fastjson.JSON;
import com.google.common.collect.ImmutableMap;
import com.label.Model.Do.Activity;
import com.label.Model.Do.Persons;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @date 2021/1/5
 */
public class ExcelUtils {

    private static ExcelUtils instance = new ExcelUtils();

    private ExcelUtils(){}

    public static ExcelUtils getInstance(){
        return instance;
    }

    String[] col={"序号","学号","姓名","签到","分数"};

    public String  createExcel(Activity activity) {

        //定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();
        //创建一个Sheet页
        XSSFSheet sheet = wb.createSheet("签到表");

        setHeadNew(sheet,wb,activity.getName()+"签到表",4);

        //获得表格第二行
        XSSFRow row = sheet.createRow(1);
        //根据数据源信息给第二行每一列设置标题
        for (int i = 0; i < col.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(col[i]);
            cell.setCellStyle(getCellStyle(wb));
        }

        XSSFRow rows;
        XSSFCell cells;
        //循环拿到的数据给所有行每一列设置对应的值
        for (int i = 0; i <= 28; i++) {
            //在这个sheet页里创建一行
            rows = sheet.createRow(i + 2);
            //给该行数据赋值
            cells = rows.createCell(0);
            cells.setCellValue(i+1);
            cells.setCellStyle(getCellStyle(wb));
        }

        //为有数据的每列设置列宽
        for (int i = 0; i < col.length; i++) {
            sheet.setColumnWidth(i, 4000);
        }

        Date date = new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        // 使用项目根目录, 文件名加上时间戳
        String path = "/home/hadoop/attrfile/" + activity.getName()+"签到表"+ ".xlsx";
        try {
            File file = new File(path);
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            wb.write(fileOutputStream);
            fileOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return path;
    }

    public static void main(String[] args) {
        System.out.println("数据加载...");
        List<List<Map<String, Object>>> test=new ArrayList<>();
        List<Map<String, Object>> mapArrayList = new ArrayList<>();
        for (int i = 0; i < 3; i++) {
            Map<String, Object> map = new HashMap<>();
            map.put("姓名", i);
            map.put("年龄", i);
            map.put("性别", i);
            mapArrayList.add(map);
        }
        test.add(mapArrayList);
        test.add(mapArrayList);
        System.out.println(JSON.toJSON(mapArrayList));

        System.out.println("数据加载完成...");
        //ExcelUtils.getInstance().createExcel(test,"文件名",);

    }

    public String[] cols=new String[]{"学号","姓名","性别","入学年份","班级","专业","政治面貌","班级职务","宿舍"};
    public String[] c=new String[]{"userid","username","sex","enrollyear","classid","majorid","politics","classposition","dormitory"};
    public Map<String,String> themeTrans=ImmutableMap.of("studentprofile","学生主题","dormitoryprofile","宿舍主题","classprofile","班级主题","majorprofile","专业主题","courseprofile","课程主题");

    public String  createNewExcel(List<List<Map<String, Object>>> mapList, String filename, String[] titles, List<Persons> personsList) throws IOException {
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("应用结果");
        //设置标题
        setHead(sheet,wb,filename);

        int count=0;
        int rownum=0;
        for(List<Map<String, Object>> maps:mapList){
            rownum=setPartExcel(sheet,wb,personsList.get(count),maps,titles[count],rownum);
            count++;
        }

        //为有数据的每列设置自动列宽
        for (int i=0;i<13;i++)
            sheet.autoSizeColumn(i);

        // 使用项目根目录, 文件名加上时间戳
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        String path = "/home/hadoop/temp/" + filename + dateFormat.format(new Date()) + ".xlsx";
        FileOutputStream fileOutputStream = new FileOutputStream(new File(path));
        wb.write(fileOutputStream);
        fileOutputStream.close();

        return path;
    }

    private XSSFCellStyle getCellStyle(XSSFWorkbook wb){
        XSSFCellStyle setBorder = wb.createCellStyle();

        setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

        XSSFFont font = wb.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 16);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示

        setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        setBorder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        setBorder.setFont(font);

        return setBorder;
    }

    private void setHead(XSSFSheet sheet,XSSFWorkbook wb,String titlename){
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(titlename);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));
        // 创建单元格文字居中样式并设置标题单元格居中
        titleCell.setCellStyle(getCellStyle(wb));
    }

    private void setHeadNew(XSSFSheet sheet,XSSFWorkbook wb,String titlename,int lastcol){
        XSSFRow titleRow = sheet.createRow(0);
        XSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(titlename);
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, lastcol));
        // 创建单元格文字居中样式并设置标题单元格居中
        titleCell.setCellStyle(getCellStyle(wb));
    }

    private int setPartExcel(XSSFSheet sheet,XSSFWorkbook wb, Persons person,List<Map<String, Object>> maps,String title,int rownum){
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        XSSFCellStyle xssfCellStyle=getCellStyle(wb);

        String[] titles=new String[]{"画像名称","创建者","主题","创建时间"};
        //设置大表头
        XSSFRow titleRow = sheet.createRow(rownum+1);
        XSSFCell titleCell = titleRow.createCell(0);
        titleCell.setCellValue(title);
        sheet.addMergedRegion(new CellRangeAddress(rownum+1,rownum+4+maps.size(), 0, 0));
        titleCell.setCellStyle(xssfCellStyle);
        //设置第一行标题
        for(int i=0;i<titles.length;i++){
            XSSFCell temp1 = titleRow.createCell(2*(i+1)-1);
            temp1.setCellValue(titles[i]);
            temp1.setCellStyle(xssfCellStyle);
            XSSFCell temp2 = titleRow.createCell(2*(i+1));
            switch (2*(i+1)) {
                case 2:temp2.setCellValue(person.getName());break;
                case 4:temp2.setCellValue(person.getAuthor());break;
                case 6:temp2.setCellValue(themeTrans.get(person.getTheme()));break;
                case 8:temp2.setCellValue(dateFormat.format(person.getCreateTime()));break;
            }
            temp2.setCellStyle(xssfCellStyle);
        }
        sheet.addMergedRegion(new CellRangeAddress(rownum+1, rownum+1, 8, 9));

        //设置中置标题
        XSSFRow middleRow = sheet.createRow(rownum+2);
        XSSFCell middleCell = middleRow.createCell(1);
        middleCell.setCellValue("人员名单");
        sheet.addMergedRegion(new CellRangeAddress(rownum+2, rownum+2, 1, 9));
        middleCell.setCellStyle(xssfCellStyle);

        //设置小标题
        XSSFRow listtleRow = sheet.createRow(rownum+3);
        for(int i=0;i<cols.length;i++){
            XSSFCell temp = listtleRow.createCell(i+1);
            temp.setCellValue(cols[i]);
            temp.setCellStyle(xssfCellStyle);
        }

        XSSFRow rows;
        XSSFCell cells;
        //循环拿到的数据给所有行每一列设置对应的值
        for (int i = 0; i < maps.size(); i++) {
            //在这个sheet页里创建一行
            rows = sheet.createRow(rownum+4+i);
            //给该行数据赋值
            for (int j = 0; j < cols.length; j++) {
                String value = String.valueOf(maps.get(i).get(c[j]));
                cells = rows.createCell(j+1);
                cells.setCellValue(value);
                cells.setCellStyle(xssfCellStyle);
            }
        }

        XSSFRow finalrow=sheet.createRow(rownum+4+maps.size());
        XSSFCell finalcell = finalrow.createCell(1);
        finalcell.setCellValue("合计："+maps.size()+"人");
        sheet.addMergedRegion(new CellRangeAddress(rownum+4+maps.size(), rownum+4+maps.size(), 1, 9));
        finalcell.setCellStyle(xssfCellStyle);

        return rownum+4+maps.size();
    }
}
